/*
 Navicat Premium Dump SQL

 Source Server         : mysql-home
 Source Server Type    : MySQL
 Source Server Version : 80043 (8.0.43)
 Source Host           : goudan.online:3306
 Source Schema         : kkh-dev

 Target Server Type    : PostgreSQL
 Target Server Version : 16
 File Encoding         : UTF8

 Date: 14/10/2025 15:07:51
 Converted from MySQL to PostgreSQL
*/

-- ----------------------------
-- Table structure for album
-- ----------------------------
DROP TABLE IF EXISTS album;
CREATE TABLE album (
                       id BIGINT NOT NULL,
                       name VARCHAR(100) NOT NULL,
                       description TEXT NULL,
                       cover_image VARCHAR(500) NULL DEFAULT NULL,
                       author_id BIGINT NOT NULL,
                       status VARCHAR(20) NOT NULL DEFAULT 'active',
                       created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                       updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                       PRIMARY KEY (id)
);

COMMENT ON TABLE album IS '相册表';
COMMENT ON COLUMN album.id IS '主键ID';
COMMENT ON COLUMN album.name IS '相册名称';
COMMENT ON COLUMN album.description IS '相册描述';
COMMENT ON COLUMN album.cover_image IS '封面图片URL';
COMMENT ON COLUMN album.author_id IS '创建者ID';
COMMENT ON COLUMN album.status IS '状态：active-启用，inactive-禁用';
COMMENT ON COLUMN album.created_at IS '创建时间';
COMMENT ON COLUMN album.updated_at IS '最后更新时间';

CREATE INDEX idx$album_createdAt ON album(created_at);

-- Create trigger for updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_album_updated_at BEFORE UPDATE ON album
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for album_photo
-- ----------------------------
DROP TABLE IF EXISTS album_photo;
CREATE TABLE album_photo (
                             id BIGINT NOT NULL,
                             album_id BIGINT NOT NULL,
                             title VARCHAR(200) NULL DEFAULT NULL,
                             description TEXT NULL,
                             image_url VARCHAR(500) NOT NULL,
                             sort_order INTEGER NOT NULL DEFAULT 0,
                             created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                             updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                             PRIMARY KEY (id)
);

COMMENT ON TABLE album_photo IS '相册照片表';
COMMENT ON COLUMN album_photo.id IS '主键ID';
COMMENT ON COLUMN album_photo.album_id IS '相册ID';
COMMENT ON COLUMN album_photo.title IS '照片标题';
COMMENT ON COLUMN album_photo.description IS '照片描述';
COMMENT ON COLUMN album_photo.image_url IS '图片URL';
COMMENT ON COLUMN album_photo.sort_order IS '排序权重';
COMMENT ON COLUMN album_photo.created_at IS '创建时间';
COMMENT ON COLUMN album_photo.updated_at IS '最后更新时间';

CREATE UNIQUE INDEX uk$albumPhoto_albumId_imageUrl ON album_photo(album_id, image_url);
CREATE INDEX idx$albumPhoto_albumId ON album_photo(album_id);
CREATE INDEX idx$albumPhoto_sortOrder ON album_photo(sort_order);
CREATE INDEX idx$albumPhoto_createdAt ON album_photo(created_at);

CREATE TRIGGER update_album_photo_updated_at BEFORE UPDATE ON album_photo
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS article;
CREATE TABLE article (
                         id BIGINT NOT NULL,
                         title VARCHAR(200) NOT NULL,
                         content TEXT NOT NULL,
                         summary TEXT NULL,
                         cover_image VARCHAR(500) NULL DEFAULT NULL,
                         author_id BIGINT NOT NULL,
                         category_id BIGINT NULL DEFAULT NULL,
                         series_id BIGINT NULL DEFAULT NULL,
                         series_order INTEGER NULL DEFAULT NULL,
                         view_count BIGINT NOT NULL DEFAULT 0,
                         like_count BIGINT NOT NULL DEFAULT 0,
                         comment_count BIGINT NOT NULL DEFAULT 0,
                         status VARCHAR(20) NOT NULL DEFAULT 'draft',
                         is_top SMALLINT NOT NULL DEFAULT 0,
                         allow_comment SMALLINT NOT NULL DEFAULT 1,
                         published_at TIMESTAMP(3) NULL DEFAULT NULL,
                         created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         PRIMARY KEY (id)
);

COMMENT ON TABLE article IS '文章表';
COMMENT ON COLUMN article.id IS '主键ID';
COMMENT ON COLUMN article.title IS '文章标题';
COMMENT ON COLUMN article.content IS '文章内容（Markdown格式）';
COMMENT ON COLUMN article.summary IS '文章摘要';
COMMENT ON COLUMN article.cover_image IS '封面图片URL';
COMMENT ON COLUMN article.author_id IS '作者ID';
COMMENT ON COLUMN article.category_id IS '分类ID';
COMMENT ON COLUMN article.series_id IS '专栏ID';
COMMENT ON COLUMN article.series_order IS '在专栏中的排序';
COMMENT ON COLUMN article.view_count IS '浏览次数';
COMMENT ON COLUMN article.like_count IS '点赞次数';
COMMENT ON COLUMN article.comment_count IS '评论次数';
COMMENT ON COLUMN article.status IS '状态：draft-草稿，published-已发布，archived-已归档';
COMMENT ON COLUMN article.is_top IS '是否置顶：0-否，1-是';
COMMENT ON COLUMN article.allow_comment IS '是否允许评论：0-否，1-是';
COMMENT ON COLUMN article.published_at IS '发布时间';
COMMENT ON COLUMN article.created_at IS '创建时间';
COMMENT ON COLUMN article.updated_at IS '最后更新时间';

CREATE INDEX idx$article_authorId ON article(author_id);
CREATE INDEX idx$article_categoryId ON article(category_id);
CREATE INDEX idx$article_seriesId ON article(series_id);
CREATE INDEX idx$article_status ON article(status);
CREATE INDEX idx$article_createdAt ON article(created_at);

-- Full text search index for PostgreSQL
CREATE INDEX idx$article_titleContent_fts ON article USING gin(to_tsvector('english', title || ' ' || content));

CREATE TRIGGER update_article_updated_at BEFORE UPDATE ON article
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for article_tag
-- ----------------------------
DROP TABLE IF EXISTS article_tag;
CREATE TABLE article_tag (
                             id BIGINT NOT NULL,
                             article_id BIGINT NOT NULL,
                             tag_id BIGINT NOT NULL,
                             created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                             PRIMARY KEY (id)
);

COMMENT ON TABLE article_tag IS '文章标签关联表';
COMMENT ON COLUMN article_tag.id IS '主键ID';
COMMENT ON COLUMN article_tag.article_id IS '文章ID';
COMMENT ON COLUMN article_tag.tag_id IS '标签ID';
COMMENT ON COLUMN article_tag.created_at IS '创建时间';

CREATE UNIQUE INDEX uk$articleTag_articleId_tagId ON article_tag(article_id, tag_id);
CREATE INDEX idx$articleTag_articleId ON article_tag(article_id);
CREATE INDEX idx$articleTag_tagId ON article_tag(tag_id);

-- ----------------------------
-- Table structure for article_view_log
-- ----------------------------
DROP TABLE IF EXISTS article_view_log;
CREATE TABLE article_view_log (
                                  id BIGINT NOT NULL,
                                  article_id BIGINT NOT NULL,
                                  ip_address VARCHAR(50) NOT NULL,
                                  user_agent VARCHAR(500) NULL DEFAULT NULL,
                                  referer VARCHAR(500) NULL DEFAULT NULL,
                                  viewed_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                  PRIMARY KEY (id)
);

COMMENT ON TABLE article_view_log IS '文章浏览记录表';
COMMENT ON COLUMN article_view_log.id IS '主键ID';
COMMENT ON COLUMN article_view_log.article_id IS '文章ID';
COMMENT ON COLUMN article_view_log.ip_address IS 'IP地址';
COMMENT ON COLUMN article_view_log.user_agent IS '用户代理';
COMMENT ON COLUMN article_view_log.referer IS '来源页面';
COMMENT ON COLUMN article_view_log.viewed_at IS '浏览时间';

CREATE INDEX idx$articleViewLog_articleId ON article_view_log(article_id);
CREATE INDEX idx$articleViewLog_ipAddress ON article_view_log(ip_address);
CREATE INDEX idx$articleViewLog_viewedAt ON article_view_log(viewed_at);

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS category;
CREATE TABLE category (
                          id BIGINT NOT NULL,
                          name VARCHAR(50) NOT NULL,
                          description VARCHAR(200) NULL DEFAULT NULL,
                          icon VARCHAR(100) NULL DEFAULT NULL,
                          sort_order INTEGER NOT NULL DEFAULT 0,
                          status VARCHAR(20) NOT NULL DEFAULT 'active',
                          created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                          updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                          PRIMARY KEY (id)
);

COMMENT ON TABLE category IS '文章分类表';
COMMENT ON COLUMN category.id IS '主键ID';
COMMENT ON COLUMN category.name IS '分类名称';
COMMENT ON COLUMN category.description IS '分类描述';
COMMENT ON COLUMN category.icon IS '分类图标';
COMMENT ON COLUMN category.sort_order IS '排序权重';
COMMENT ON COLUMN category.status IS '状态：active-启用，inactive-禁用';
COMMENT ON COLUMN category.created_at IS '创建时间';
COMMENT ON COLUMN category.updated_at IS '最后更新时间';

CREATE UNIQUE INDEX uk$category_name ON category(name);
CREATE INDEX idx$category_sortOrder ON category(sort_order);

CREATE TRIGGER update_category_updated_at BEFORE UPDATE ON category
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for comment
-- ----------------------------
DROP TABLE IF EXISTS comment;
CREATE TABLE comment (
                         id BIGINT NOT NULL,
                         article_id BIGINT NOT NULL,
                         user_id BIGINT NOT NULL,
                         nickname VARCHAR(50) NOT NULL,
                         parent_id BIGINT NULL DEFAULT NULL,
                         content TEXT NOT NULL,
                         ip_address VARCHAR(50) NULL DEFAULT NULL,
                         user_agent VARCHAR(500) NULL DEFAULT NULL,
                         like_count INTEGER NOT NULL DEFAULT 0,
                         status VARCHAR(20) NOT NULL DEFAULT 'pending',
                         created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         PRIMARY KEY (id)
);

COMMENT ON TABLE comment IS '评论表';
COMMENT ON COLUMN comment.id IS '主键ID';
COMMENT ON COLUMN comment.article_id IS '文章ID';
COMMENT ON COLUMN comment.user_id IS '用户ID（可为空，支持匿名评论）';
COMMENT ON COLUMN comment.nickname IS '评论者昵称';
COMMENT ON COLUMN comment.parent_id IS '父评论ID（用于回复功能）';
COMMENT ON COLUMN comment.content IS '评论内容';
COMMENT ON COLUMN comment.ip_address IS 'IP地址';
COMMENT ON COLUMN comment.user_agent IS '用户代理';
COMMENT ON COLUMN comment.like_count IS '点赞次数';
COMMENT ON COLUMN comment.status IS '状态：pending-待审核，approved-已通过，rejected-已拒绝';
COMMENT ON COLUMN comment.created_at IS '创建时间';
COMMENT ON COLUMN comment.updated_at IS '最后更新时间';

CREATE INDEX idx$comment_articleId ON comment(article_id);
CREATE INDEX idx$comment_userId ON comment(user_id);
CREATE INDEX idx$comment_parentId ON comment(parent_id);
CREATE INDEX idx$comment_status ON comment(status);
CREATE INDEX idx$comment_createdAt ON comment(created_at);

CREATE TRIGGER update_comment_updated_at BEFORE UPDATE ON comment
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for friend_link
-- ----------------------------
DROP TABLE IF EXISTS friend_link;
CREATE TABLE friend_link (
                             id BIGINT NOT NULL,
                             name VARCHAR(100) NOT NULL,
                             url VARCHAR(500) NOT NULL,
                             logo VARCHAR(500) NOT NULL,
                             description VARCHAR(200) NULL DEFAULT NULL,
                             category VARCHAR(50) NULL DEFAULT 'default',
                             sort_order INTEGER NOT NULL DEFAULT 0,
                             status VARCHAR(20) NOT NULL DEFAULT 'active',
                             created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                             updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                             PRIMARY KEY (id)
);

COMMENT ON TABLE friend_link IS '友链表';
COMMENT ON COLUMN friend_link.id IS '主键ID';
COMMENT ON COLUMN friend_link.name IS '友链名称';
COMMENT ON COLUMN friend_link.url IS '友链URL';
COMMENT ON COLUMN friend_link.logo IS '友链Logo';
COMMENT ON COLUMN friend_link.description IS '友链描述';
COMMENT ON COLUMN friend_link.category IS '友链分类';
COMMENT ON COLUMN friend_link.sort_order IS '排序权重';
COMMENT ON COLUMN friend_link.status IS '状态：active-启用，inactive-禁用';
COMMENT ON COLUMN friend_link.created_at IS '创建时间';
COMMENT ON COLUMN friend_link.updated_at IS '最后更新时间';

CREATE TRIGGER update_friend_link_updated_at BEFORE UPDATE ON friend_link
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS message;
CREATE TABLE message (
                         id BIGINT NOT NULL,
                         board_id BIGINT NOT NULL,
                         user_id BIGINT NULL DEFAULT NULL,
                         nickname VARCHAR(50) NOT NULL,
                         email VARCHAR(100) NULL DEFAULT NULL,
                         website VARCHAR(200) NULL DEFAULT NULL,
                         avatar VARCHAR(500) NULL DEFAULT NULL,
                         content TEXT NOT NULL,
                         ip_address VARCHAR(50) NULL DEFAULT NULL,
                         user_agent VARCHAR(500) NULL DEFAULT NULL,
                         status VARCHAR(20) NOT NULL DEFAULT 'pending',
                         created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         PRIMARY KEY (id)
);

COMMENT ON TABLE message IS '留言表';
COMMENT ON COLUMN message.id IS '主键ID';
COMMENT ON COLUMN message.board_id IS '留言板ID';
COMMENT ON COLUMN message.user_id IS '用户ID（可为空，支持匿名留言）';
COMMENT ON COLUMN message.nickname IS '留言者昵称';
COMMENT ON COLUMN message.email IS '留言者邮箱';
COMMENT ON COLUMN message.website IS '留言者网站';
COMMENT ON COLUMN message.avatar IS '留言者头像';
COMMENT ON COLUMN message.content IS '留言内容';
COMMENT ON COLUMN message.ip_address IS 'IP地址';
COMMENT ON COLUMN message.user_agent IS '用户代理';
COMMENT ON COLUMN message.status IS '状态：pending-待审核，approved-已通过，rejected-已拒绝';
COMMENT ON COLUMN message.created_at IS '创建时间';
COMMENT ON COLUMN message.updated_at IS '最后更新时间';

CREATE INDEX idx$message_boardId ON message(board_id);
CREATE INDEX idx$message_userId ON message(user_id);
CREATE INDEX idx$message_status ON message(status);
CREATE INDEX idx$message_createdAt ON message(created_at);

CREATE TRIGGER update_message_updated_at BEFORE UPDATE ON message
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for message_board
-- ----------------------------
DROP TABLE IF EXISTS message_board;
CREATE TABLE message_board (
                               id BIGINT NOT NULL,
                               name VARCHAR(100) NOT NULL,
                               description TEXT NULL,
                               status VARCHAR(20) NOT NULL DEFAULT 'active',
                               created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                               updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                               PRIMARY KEY (id)
);

COMMENT ON TABLE message_board IS '留言板表';
COMMENT ON COLUMN message_board.id IS '主键ID';
COMMENT ON COLUMN message_board.name IS '留言板名称';
COMMENT ON COLUMN message_board.description IS '留言板描述';
COMMENT ON COLUMN message_board.status IS '状态：active-启用，inactive-禁用';
COMMENT ON COLUMN message_board.created_at IS '创建时间';
COMMENT ON COLUMN message_board.updated_at IS '最后更新时间';

CREATE TRIGGER update_message_board_updated_at BEFORE UPDATE ON message_board
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for series
-- ----------------------------
DROP TABLE IF EXISTS series;
CREATE TABLE series (
                        id BIGINT NOT NULL,
                        name VARCHAR(100) NOT NULL,
                        description TEXT NULL,
                        cover_image VARCHAR(500) NULL DEFAULT NULL,
                        author_id BIGINT NOT NULL,
                        article_count INTEGER NOT NULL DEFAULT 0,
                        status VARCHAR(20) NOT NULL DEFAULT 'active',
                        created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (id)
);

COMMENT ON TABLE series IS '文章专栏表';
COMMENT ON COLUMN series.id IS '主键ID';
COMMENT ON COLUMN series.name IS '专栏名称';
COMMENT ON COLUMN series.description IS '专栏描述';
COMMENT ON COLUMN series.cover_image IS '封面图片URL';
COMMENT ON COLUMN series.author_id IS '作者ID';
COMMENT ON COLUMN series.article_count IS '文章数量';
COMMENT ON COLUMN series.status IS '状态：active-启用，inactive-禁用';
COMMENT ON COLUMN series.created_at IS '创建时间';
COMMENT ON COLUMN series.updated_at IS '最后更新时间';

CREATE INDEX idx$series_authorId ON series(author_id);
CREATE INDEX idx$series_createdAt ON series(created_at);

CREATE TRIGGER update_series_updated_at BEFORE UPDATE ON series
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for sys_file
-- ----------------------------
DROP TABLE IF EXISTS sys_file;
CREATE TABLE sys_file (
                          id BIGINT NOT NULL,
                          file_name VARCHAR(255) NOT NULL,
                          bucket VARCHAR(255) NOT NULL,
                          object_key VARCHAR(255) NOT NULL,
                          file_digest VARCHAR(64) NOT NULL,
                          file_size BIGINT NOT NULL,
                          mime_type VARCHAR(100) NOT NULL,
                          oss_url VARCHAR(500) NOT NULL,
                          upload_user_id BIGINT NOT NULL,
                          upload_at TIMESTAMP(3) NOT NULL,
                          created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                          updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                          PRIMARY KEY (id)
);

COMMENT ON TABLE sys_file IS '系统文件表';
COMMENT ON COLUMN sys_file.id IS '主键ID';
COMMENT ON COLUMN sys_file.file_name IS '文件原始名称';
COMMENT ON COLUMN sys_file.bucket IS '在oss中的桶名称';
COMMENT ON COLUMN sys_file.object_key IS '文件在oss中的key';
COMMENT ON COLUMN sys_file.file_digest IS '文件唯一标识（MD5或SHA256）';
COMMENT ON COLUMN sys_file.file_size IS '文件大小（字节）';
COMMENT ON COLUMN sys_file.mime_type IS '文件类型（如 image/jpeg,application/pdf）';
COMMENT ON COLUMN sys_file.oss_url IS 'OSS 文件存储地址';
COMMENT ON COLUMN sys_file.upload_user_id IS '上传者用户ID';
COMMENT ON COLUMN sys_file.upload_at IS '文件上传时间';
COMMENT ON COLUMN sys_file.created_at IS '创建时间';
COMMENT ON COLUMN sys_file.updated_at IS '最后更新时间';

CREATE INDEX idx$sysFile_uploadUserId ON sys_file(upload_user_id);
CREATE INDEX idx$sysFile_uploadAt ON sys_file(upload_at);

CREATE TRIGGER update_sys_file_updated_at BEFORE UPDATE ON sys_file
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ----------------------------
-- Table structure for sys_login_method
-- ----------------------------
DROP TABLE IF EXISTS sys_login_method;
CREATE TABLE sys_login_method (
                                  id BIGINT NOT NULL,
                                  user_id BIGINT NOT NULL,
                                  method_type VARCHAR(50) NOT NULL,
                                  identifier VARCHAR(255) NOT NULL,
                                  access_token VARCHAR(255) NULL DEFAULT NULL,
                                  expires_at TIMESTAMP(3) NULL DEFAULT NULL,
                                  created_at TIMESTAMP(3) NOT NULL,
                                  updated_at TIMESTAMP(3) NOT NULL,
                                  PRIMARY KEY (id)
);

COMMENT ON TABLE sys_login_method IS '登录方式表';
COMMENT ON COLUMN sys_login_method.id IS '主键ID';
COMMENT ON COLUMN sys_login_method.user_id IS '用户ID';
COMMENT ON COLUMN sys_login_method.method_type IS '登录方式(''email'',''username'',''github'',''wechat'',''gitee''）';
COMMENT ON COLUMN sys_login_method.identifier IS '邮箱、用户名、第三方平台用户ID';
COMMENT ON COLUMN sys_login_method.access_token IS '密码，access_token';
COMMENT ON COLUMN sys_login_method.expires_at IS '如果第三方平台登录，令牌的过期时间';
COMMENT ON COLUMN sys_login_method.created_at IS '创建时间';
COMMENT ON COLUMN sys_login_method.updated_at IS '更新时间';

CREATE UNIQUE INDEX uk$sysLoginMethod_methodType_identifier ON sys_login_method(method_type, identifier);

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS sys_role;
CREATE TABLE sys_role (
                          id BIGINT NOT NULL,
                          name VARCHAR(50) NOT NULL,
                          code VARCHAR(50) NOT NULL,
                          description VARCHAR(255) NULL DEFAULT NULL,
                          is_default INTEGER NOT NULL DEFAULT 0,
                          created_at TIMESTAMP(3) NOT NULL,
                          updated_at TIMESTAMP(3) NOT NULL,
                          PRIMARY KEY (id)
);

COMMENT ON TABLE sys_role IS '角色表';
COMMENT ON COLUMN sys_role.id IS '角色ID';
COMMENT ON COLUMN sys_role.name IS '角色名称';
COMMENT ON COLUMN sys_role.code IS '角色名称';
COMMENT ON COLUMN sys_role.description IS '角色描述';
COMMENT ON COLUMN sys_role.is_default IS '是否默认角色：0-否，1-是';
COMMENT ON COLUMN sys_role.created_at IS '创建时间';
COMMENT ON COLUMN sys_role.updated_at IS '更新时间';

CREATE UNIQUE INDEX uk$sysRole_name ON sys_role(name);

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS sys_user;
CREATE TABLE sys_user (
                          id BIGINT NOT NULL,
                          nickname VARCHAR(255) NOT NULL,
                          avatar VARCHAR(255) NULL DEFAULT NULL,
                          introduction VARCHAR(255) NULL DEFAULT NULL,
                          created_at TIMESTAMP(3) NOT NULL,
                          updated_at TIMESTAMP(3) NOT NULL,
                          PRIMARY KEY (id)
);

COMMENT ON TABLE sys_user IS '用户表';
COMMENT ON COLUMN sys_user.id IS 'id';
COMMENT ON COLUMN sys_user.nickname IS '昵称（默认账户名）';
COMMENT ON COLUMN sys_user.avatar IS '头像';
COMMENT ON COLUMN sys_user.introduction IS '简介';
COMMENT ON COLUMN sys_user.created_at IS '创建时间';
COMMENT ON COLUMN sys_user.updated_at IS '更新时间';

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS sys_user_role;
CREATE TABLE sys_user_role (
                               id BIGINT NOT NULL,
                               user_id BIGINT NOT NULL,
                               role_id BIGINT NOT NULL,
                               created_at TIMESTAMP(3) NOT NULL,
                               updated_at TIMESTAMP(3) NOT NULL,
                               PRIMARY KEY (id)
);

COMMENT ON TABLE sys_user_role IS '用户角色关联表';
COMMENT ON COLUMN sys_user_role.id IS '主键ID';
COMMENT ON COLUMN sys_user_role.user_id IS '用户ID';
COMMENT ON COLUMN sys_user_role.role_id IS '角色ID';
COMMENT ON COLUMN sys_user_role.created_at IS '创建时间';
COMMENT ON COLUMN sys_user_role.updated_at IS '更新时间';

CREATE UNIQUE INDEX uk$sysUserRole_userId_roleId ON sys_user_role(user_id, role_id);
CREATE INDEX idx$sysUserRole_userId ON sys_user_role(user_id);
CREATE INDEX idx$sysUserRole_roleId ON sys_user_role(role_id);

-- ----------------------------
-- Table structure for tag
-- ----------------------------
DROP TABLE IF EXISTS tag;
CREATE TABLE tag (
                     id BIGINT NOT NULL,
                     name VARCHAR(50) NOT NULL,
                     color VARCHAR(20) NULL DEFAULT '#1890ff',
                     description VARCHAR(200) NULL DEFAULT NULL,
                     article_count INTEGER NOT NULL DEFAULT 0,
                     status VARCHAR(20) NOT NULL DEFAULT 'active',
                     created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                     updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
                     PRIMARY KEY (id)
);

COMMENT ON TABLE tag IS '文章标签表';
COMMENT ON COLUMN tag.id IS '主键ID';
COMMENT ON COLUMN tag.name IS '标签名称';
COMMENT ON COLUMN tag.color IS '标签颜色';
COMMENT ON COLUMN tag.description IS '标签描述';
COMMENT ON COLUMN tag.article_count IS '使用该标签的文章数量';
COMMENT ON COLUMN tag.status IS '状态：active-启用，inactive-禁用';
COMMENT ON COLUMN tag.created_at IS '创建时间';
COMMENT ON COLUMN tag.updated_at IS '最后更新时间';

CREATE UNIQUE INDEX uk$tag_name ON tag(name);
CREATE INDEX idx$tag_status ON tag(status);
CREATE INDEX idx$tag_articleCount ON tag(article_count);

CREATE TRIGGER update_tag_updated_at BEFORE UPDATE ON tag
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();




INSERT INTO sys_user (id, nickname, avatar, introduction, created_at, updated_at)
VALUES (1, '管理员', NULL, '系统管理员', NOW(), NOW());

INSERT INTO sys_login_method (id, user_id, method_type, identifier, access_token, expires_at,
                              created_at, updated_at)
VALUES (1, 1, 'username',
        'admin', '$2a$10$9Ul0Zi6cgyg6dyaX9zRuMOnvoraXakqTRbjNsOyWukAaQDVdlvSQK', NULL,
        NOW(), NOW());

INSERT INTO sys_role (id, name, code, description, is_default, created_at, updated_at)
values (1, '管理员', 'ADMIN', '系统管理员角色，拥有所有权限', 0, NOW(), NOW()),
       (2, '用户', 'USER', '普通访客角色，权限受限', 1, NOW(), NOW());

INSERT INTO sys_user_role (id, user_id, role_id, created_at, updated_at)
values (1, 1,
        1, NOW(), NOW());


-- 插入默认留言板
INSERT INTO message_board (id, name, description)
VALUES ('1', '默认留言板', '欢迎在这里留言交流！');
